const express = require('express')
const router = express.Router()
const sqlite3 = require('sqlite3')
const dbName = 'devices.sqlite'
const db = new sqlite3.Database(dbName)
const xlsx = require('xlsx')
const util = require('../utils/util')
const consts = require('../consts/Consts')

db.configure('trace', () => {
  console.log('trace:%s', this)
})
db.serialize(() => {
  // 用户表
  const sql_crate_user = `
        CREATE TABLE IF NOT EXISTS user
        (
        phoneNumber Text primary key,
        name Text,
        pwd Text,
        registerTime Text
        )
    `
  // 航标类型表
  const sql_crate_navigationType = `
        CREATE TABLE IF NOT EXISTS navigationType
        (
        code integer primary key autoincrement NOT NULL,
        NavigationType Text NOT NULL UNIQUE
        )
    `
  const sql_crate_attribute = `
        CREATE TABLE IF NOT EXISTS attribute
        (
        code integer primary key autoincrement,
        Attribute Text NOT NULL UNIQUE
        )
    `
  const sql_crate_divisionName = `
        CREATE TABLE IF NOT EXISTS divisionName
        (
        code integer primary key autoincrement,
        DivisionName Text NOT NULL UNIQUE
        )
    `
  const sql_crate_channelName = `
        CREATE TABLE IF NOT EXISTS channelName
        (
        code integer primary key autoincrement,
        ChannelName Text NOT NULL UNIQUE
        )
    `
  const sql_crate_seaRoute = `
        CREATE TABLE IF NOT EXISTS seaRoute
        (
        code integer primary key autoincrement,
        SeaRoute Text NOT NULL UNIQUE
        )
    `
  // 海区表
  const sql_crate_areaName = `
        CREATE TABLE IF NOT EXISTS areaName
        (
        code integer primary key autoincrement,
        AreaName Text NOT NULL UNIQUE
        )
    `
  // 航标信息表
  const sql_crate_mark = `
        CREATE TABLE IF NOT EXISTS mark
        (
        AtoNGUID Text primary key,
        AtoNName Text NOT NULL UNIQUE,
        AtoNCode Text,
        NavigationTypeName Text,
        NavigationType integer,
        Attribute integer,
        LongitudeDegree real,
        LongitudeMinute real,
        LongitudeSecond real,
        LatitudeDegree real,
        LatitudeMinute real,
        LatitudeSecond real,
        AtoNType Text,
        DivisionName integer,
        ChannelName integer,
        SeaRoute integer,
        ShownOnSeaChart integer,
        IsTelemetryAndTelecontrol integer,
        LightHeight real,
        LightCharacteristics Text,
        AreaName integer,
        SortNo TEXT,
        FOREIGN KEY (NavigationType)
        REFERENCES navigationType(code),
        FOREIGN KEY (Attribute)
        REFERENCES attribute(code),
        FOREIGN KEY (DivisionName)
        REFERENCES divisionName(code),
        FOREIGN KEY (ChannelName)
        REFERENCES channelName(code),
        FOREIGN KEY (SeaRoute)
        REFERENCES seaRoute(code),
        FOREIGN KEY (AreaName)
        REFERENCES areaName(code)
        )
    `
  // 任务表
  const sql_crate_task = `
        CREATE TABLE IF NOT EXISTS task
        (
        Code Text primary key,
        TaskType integer,
        AtoNName Text,
        NavigationType integer,
        ChannelName integer,
        SeaRoute integer,
        AreaName integer,
        Longtitude Text,
        Latitude Text,
        BeforeLongtitude Text,
        BeforeLatitude Text,
        UploadLongtitude Text,
        UploadLatitude Text,
        Person Text,
        BoatName Text,
        Distance Text,
        Result integer,
        Complete integer default 0,
        StartTime Text,
        CompleteTime Text,
        PublishTime Text,
        Elevation Text,
        Files Text,
        ReMark Text,
        TaskDesc Text,
        FOREIGN KEY (NavigationType)
        REFERENCES navigationType(code),
        FOREIGN KEY (ChannelName)
        REFERENCES channelName(code),
        FOREIGN KEY (SeaRoute)
        REFERENCES seaRoute(code),
        FOREIGN KEY (AreaName)
        REFERENCES areaName(code)
        )
  `
  db.run(sql_crate_navigationType)
  db.run(sql_crate_attribute)
  db.run(sql_crate_channelName)
  db.run(sql_crate_areaName)
  db.run(sql_crate_divisionName)
  db.run(sql_crate_seaRoute)
  db.run(sql_crate_mark)
  db.run(sql_crate_user)
  db.run(sql_crate_task)
  const sql_if_mark_has_data = 'SELECT count(*) as kk FROM mark'
  db.each(sql_if_mark_has_data, (err, row) => {
    if (err) throw err
    console.log('mark count: %o', row)
    if (row.kk < 1) {
      initExcel()
    }
  })
})

function initExcel() {
  console.log('import the data form excel to sqlite')
  const workbook = xlsx.readFile('public/MarkInfo.xlsx')
  const sheetNames = workbook.SheetNames
  const sheet = workbook.Sheets[sheetNames[0]]
  const thead = [sheet.A1.v, sheet.B1.v, sheet.C1.v, sheet.D1.v, sheet.E1.v, sheet.F1.v, sheet.G1.v, sheet.H1.v, sheet.I1.v, sheet.J1.v, sheet.K1.v, sheet.L1.v, sheet.M1.v, sheet.N1.v, sheet.O1.v, sheet.P1.v, sheet.Q1.v, sheet.R1.v, sheet.S1.v, sheet.T1.v, sheet.U1.v, sheet.V1.v]
  console.log('thead: ' + thead)
  const data = xlsx.utils.sheet_to_json(sheet)
  console.log('excel data length: ' + data.length)
  for (let i = 0; i < data.length; i++) {
    var values = {}
    for (let j = 0; j < 22; j++) {
      values[thead[j]] = data[i][thead[j]]
    }
    // NavigationType
    const sql_insert_NavigationType = 'INSERT OR IGNORE INTO navigationType (NavigationType) values(?)'
    db.run(sql_insert_NavigationType, [values.NavigationType], (err) => {
      if (err) throw err
    })
    // Attribute
    const sql_insert_Attribute = 'INSERT OR IGNORE INTO attribute (Attribute) values(?)'
    db.run(sql_insert_Attribute, [values.Attribute], (err) => {
      if (err) throw err
    })
    // DivisionName
    const sql_insert_DivisionName = 'INSERT OR IGNORE INTO divisionName (DivisionName) values(?)'
    db.run(sql_insert_DivisionName, [values.DivisionName], (err) => {
      if (err) throw err
    })
    // ChannelName
    const sql_insert_ChannelName = 'INSERT OR IGNORE INTO channelName (ChannelName) values(?)'
    db.run(sql_insert_ChannelName, [values.ChannelName], (err) => {
      if (err) throw err
    })
    // SeaRoute
    const sql_insert_SeaRoute = 'INSERT OR IGNORE INTO seaRoute (SeaRoute) values(?)'
    db.run(sql_insert_SeaRoute, [values.SeaRoute], (err) => {
      if (err) throw err
    })
    // AreaName
    const sql_insert_AreaName = 'INSERT OR IGNORE INTO areaName (AreaName) values(?)'
    db.run(sql_insert_AreaName, [values.AreaName], (err) => {
      if (err) throw err
    })

    // MARKINFO
    const sql_add_mark = 'INSERT INTO mark VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql_add_mark, [values.AtoNGUID, values.AtoNName, values.AtoNCode, values.NavigationTypeName, values.NavigationType, values.Attribute, values.LongitudeDegree, values.LongitudeMinute, values.LongitudeSecond, values.LatitudeDegree, values.LatitudeMinute, values.LatitudeSecond, values.AtoNType, values.DivisionName, values.ChannelName, values.SeaRoute, values.ShownOnSeaChart, values.IsTelemetryAndTelecontrol, values.LightHeight, values.LightCharacteristics, values.AreaName, values.SortNo], (err, row) => {
      if (err) {
        console.log('import excel: %o', row)
        console.log('import excel: %o', values)
        console.error(err)
      }
    })
  }
  const sql_count = 'SELECT COUNT(*) as kk FROM mark'
  db.get(sql_count, (err, row) => {
    if (err) throw err
    console.log('sql_count mark: %o ', row)
  })
}

function getLaDFM(data) {
  return data.LaDFM.du + '°' + data.LaDFM.fen + '\'' + data.LaDFM.miao + '\'\''
}

function getLongDFM(data) {
  return data.LongDFM.du + '°' + data.LongDFM.fen + '\'' + data.LongDFM.miao + '\'\''
}

// web端任务
const TaskDb = class TaskDb {
  static delTask(code, cb) {
    db.run('DELETE FROM task where Code = ?', code, cb)
  }

  static getTaskCount(taskType, cb) {
    switch (taskType) {
      case consts.TASK_TYPE_SET_MARK:
        db.all('select count(Code) as SET_MARK from task where TaskType = ?', taskType, cb)
        break
      case consts.TASK_TYPE_CHANGE_MARK:
        db.all('select count(Code) as CHANGE_MARK from task where TaskType = ?', taskType, cb)
        break
      case consts.TASK_TYPE_REMOVE_MARK:
        db.all('select count(Code) as REMOVE_MARK from task where TaskType = ?', taskType, cb)
        break
      case consts.TASK_TYPE_CHECK_MARK:
        db.all('select count(Code) as CHECK_MARK from task where TaskType = ?', taskType, cb)
        break
    }
  }

  // 获取已巡检的数量
  static getCheckedCount(cb) {
    db.all('select count(Code) as CheckedCount from task where TaskType = ? and Complete = 1', consts.TASK_TYPE_CHECK_MARK, cb)
  }

  // 获取巡检异常数量
  static getUnUsualCount(cb) {
    db.all('select count(Code) as UnUsualCount from task where TaskType = ? and Complete = 1 and Result = 0', consts.TASK_TYPE_CHECK_MARK, cb)
  }

  // 获取已维修数量
  static getMaintainedCount(cb) {
    db.all('select count(Code) as MaintainedCount from task where TaskType = ? and Result = 1', consts.TASK_TYPE_MAINTAIN_MARK, cb)
  }

  // 获取未维修数量
  static getUnMaintainedCount(cb) {
    db.all('select count(Code)  as UnMaintainedCount from task where TaskType = ? and Result = 0', consts.TASK_TYPE_MAINTAIN_MARK, cb)
  }

  static getTaskPage(cb) {
    console.log('TaskDb-getTaskPage')
    db.all('SELECT * FROM task', cb)
  }

  static getTaskList(taskType, cb) {
    console.log('TaskDb-getSetTaskList')
    // db.all('SELECT ttitude ,t.NextLatitude,t.PublishTime,t.Person,u.Name FROM task as t INNER JOIN user AS u on t.Person = u.phoneNumber WHERE  t.Person = ? AND t.Complete  < 1 order by t.PublishTime desc', phoneNumber, cb)

    db.all('SELECT t.*,u.Name FROM task as t inner join user as u on t.Person = u.phoneNumber where t.TaskType = ? order by t.PublishTime desc', taskType, cb)
  }

  static addSetTask(data, cb) {
    console.log('TaskDb-addSetTask')
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude, PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_SET_MARK, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addRemoveTask(data, cb) {
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude, PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_REMOVE_MARK, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addCheckTask(data, cb) {
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude, PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_CHECK_MARK, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addMaintainTask(data, cb) {
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude, PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_MAINTAIN_MARK, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addChangeTask(data, cb) {
    console.log('TaskDb-addChangeTask')
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude,PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_CHANGE_MARK, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addRetestTask(data, cb) {
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, NavigationType, ChannelName, SeaRoute, AreaName, Person, Longtitude, Latitude,PublishTime) VALUES(?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_RETEST, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.Person, getLongDFM(data), getLaDFM(data), util.yyyyMMddhhmmss()], cb)
  }

  static addTechMeasureTask(data, cb) {
    const sql = 'INSERT INTO task ( Code,TaskType, AtoNName, Person, TaskDesc ,PublishTime) VALUES(?,?,?,?,?,?)'
    db.run(sql, [data.Code, consts.TASK_TYPE_TECH, data.AtoNName, data.Person, data.TaskDesc, util.yyyyMMddhhmmss()], cb)
  }

  /**
   * Desc: '65',
   UploadLongtitude: '0.0',
   UploadLatitude: '0.0',
   Code: 'NG20210726145045',
   CompleteTime: '202107210-12:33:31',
   Result: 'true'

   * @param data
   * @param cb
   */
  static updateTask(data, paths, cb) {
    const sql = 'UPDATE task set BeforeLatitude=?,BeforeLongtitude=?,Distance=?,StartTime=?,BoatName=?,Elevation=?,UploadLongtitude=?,UploadLatitude=?,Result=?,Complete=?,CompleteTime=?,Files=?,ReMark=? where Code = ?'
    db.run(sql, [data.BeforeLatitude, data.BeforeLongtitude, data.Distance, data.StartTime, data.BoatName, data.Elevation, data.UploadLongtitude, data.UploadLatitude, data.Result, 1, data.CompleteTime, paths, data.Desc, data.Code], cb)
  }
}

// 手机端任务
const TaskMobileDb = class TaskMobileDb {
  static getAllTasks(phoneNumber, cb) {
    console.log('TaskMobileDb-getAllTasks')
    // 下面查询时字段加括号报错
    // db.all('SELECT (Code,AtoNName, ChannelName, SeaRoute ,AreaName, Longtitude ,Latitude) FROM task  WHERE Person = ? AND Complete > 0', phoneNumber, cb)
    // db.all('SELECT t.Code,t.AtoNName, t.ChannelName, t.SeaRoute ,t.AreaName, t.Longtitude ,t.Latitude FROM task as t WHERE Person = ? AND Complete > 0', phoneNumber, cb)
    db.all('SELECT t.Code,t.TaskType,t.AtoNName,t.TaskDesc, t.ChannelName, t.SeaRoute ,t.AreaName, t.Longtitude ,t.Latitude,t.PublishTime,t.Person,u.Name FROM task as t INNER JOIN user AS u on t.Person = u.phoneNumber WHERE  t.Person = ? AND t.Complete  < 1 order by t.PublishTime desc', phoneNumber, cb)
  }

  static commitSetTaskList(data, cb) {
    console.log('TaskMobileDb-commitSetTaskList')
    const sql = 'UPDATE INTO task ( Code, Longtitude, Latitude,Time,) VALUES(?,?,?,?,?,?,?)'
    db.run(sql, [data.Code, data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.Longtitude, data.Latitude], cb)
  }
}

const MarkDb = class MarkDb {

  static addMark(data, cb) {
    const sql = 'INSERT INTO mark ( AtoNGUID, AtoNName, NavigationType, ChannelName, SeaRoute,AreaName ,LongitudeDegree,LongitudeMinute,LongitudeSecond,LatitudeDegree,LatitudeMinute,LatitudeSecond) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)'
    db.run(sql, [util.guid(), data.AtoNName, data.NavigationType, data.ChannelName, data.SeaRoute, data.AreaName, data.LongDFM.du, data.LongDFM.fen, data.LongDFM.miao, data.LaDFM.du, data.LaDFM.fen, data.LaDFM.miao], cb)
  }

  static addNavigationType(type, cb) {
    db.run('INSERT INTO navigationType ( NavigationType ) VALUES(?)', type, cb)
  }

  static deleteNavigationType(type, cb) {
    db.run('DELETE FROM navigationType where NavigationType = ?', type, cb)
  }

  static getNavigationTypeList(cb) {
    db.all('SELECT (NavigationType) FROM navigationType', cb)
  }

  static addAttribute(attribute, cb) {
    db.run('INSERT INTO attribute ( Attribute ) VALUES(?)', attribute, cb)
  }

  static deleteAttribute(attribute, cb) {
    db.run('DELETE FROM attribute where Attribute = ?', attribute, cb)
  }

  static getAttributeList(cb) {
    db.all('SELECT (Attribute) FROM attribute', cb)
  }

  static addDivisionName(value, cb) {
    db.run('INSERT INTO divisionName ( DivisionName ) VALUES(?)', value, cb)
  }

  static deleteDivisionName(value, cb) {
    db.run('DELETE FROM divisionName where DivisionName = ?', value, cb)
  }

  static getDivisionNameList(cb) {
    db.all('SELECT (DivisionName) FROM divisionName', cb)
  }

  static addChannelName(value, cb) {
    db.run('INSERT INTO channelName ( ChannelName ) VALUES(?)', value, cb)
  }

  static deleteChannelName(value, cb) {
    db.run('DELETE FROM channelName where ChannelName = ?', value, cb)
  }

  static getChannelNameList(cb) {
    db.all('SELECT (ChannelName) FROM channelName', cb)
  }

  static addSeaRoute(value, cb) {
    db.run('INSERT INTO seaRoute ( SeaRoute ) VALUES(?)', value, cb)
  }

  static deleteSeaRoute(value, cb) {
    db.run('DELETE FROM seaRoute where SeaRoute = ?', value, cb)
  }

  static getSeaRouteList(cb) {
    db.all('SELECT (SeaRoute) FROM seaRoute', cb)
  }

  static addAreaName(value, cb) {
    db.run('INSERT INTO areaName ( AreaName ) VALUES(?)', value, cb)
  }

  static deleteAreaName(value, cb) {
    db.run('DELETE FROM areaName where AreaName = ?', value, cb)
  }

  static getAreaNameList(cb) {
    db.all('SELECT (AreaName) FROM areaName', cb)
  }

  static search(data, cb) {
    let NavigationType = data.NavigationType
    let ChannelName = data.ChannelName
    let SeaRoute = data.SeaRoute
    let AtoNName = data.AtoNName
    var values = []
    var conditions = []
    var sql = 'select * from mark where'
    if (!util.isEmpty(NavigationType)) {
      values.push(NavigationType)
      conditions.push(' NavigationType = ?')
    }
    if (!util.isEmpty(ChannelName)) {
      values.push(ChannelName)
      conditions.push(' ChannelName = ?')
    }
    if (!util.isEmpty(SeaRoute)) {
      values.push(SeaRoute)
      conditions.push(' SeaRoute = ?')
    }
    if (!util.isEmpty(AtoNName)) {
      values.push('%' + AtoNName + '%')
      conditions.push(' AtoNName like ?')
    }
    if (conditions.length == 4) {
      sql += conditions[0] + ' and' + conditions[1] + ' and' + conditions[2] + ' and' + conditions[3]
    } else if (conditions.length == 3) {
      sql += conditions[0] + ' and' + conditions[1] + ' and' + conditions[2]
    } else if (conditions.length == 2) {
      sql += conditions[0] + ' and' + conditions[1]
    } else if (conditions.length == 1) {
      sql += conditions[0]
    }
    console.log('db-search sql:%s', sql)
    console.log('db-search values:%o', values)
    db.all(sql, values, cb)
  }
}

const UserMobileDb = class UserMobileDb {
  static get(phoneNumber, cb) {
    db.get('SELECT phoneNumber, pwd FROM user where phoneNumber = ?', phoneNumber, cb)
  }
}

const UserDb = class UserDb {
  static all(cb) {
    db.all('SELECT phoneNumber, name FROM user', cb)
  }

  static page(cb) {
    console.log('db-page')
    const sql = 'SELECT phoneNumber, name FROM user ORDER BY phoneNumber limit 10 offset 0'
    db.run(sql, cb)
  }

  static insert(data, cb) {
    console.log('db-insert..........')
    const sql = 'INSERT INTO user ( phoneNumber, name, pwd ) VALUES(?,?,?)'
    db.run(sql, data.phoneNumber, data.name, data.pwd, cb)
  }

  static query(phoneNumber, cb) {
    const sql = 'SELECT * FROM user WHERE phoneNumber = ?'
    db.get(sql, phoneNumber, cb)
  }

  static delete(phoneNumber, cb) {
    const sql = 'DELETE FROM user WHERE phoneNumber = ?'
    db.run(sql, phoneNumber, cb)
  }
}
module.exports = router
module.exports.UserDb = UserDb
module.exports.MarkDb = MarkDb
module.exports.TaskDb = TaskDb
module.exports.TaskMobileDb = TaskMobileDb
module.exports.UserMobileDb = UserMobileDb
